Step 5: SQL Queries & Use Cases (Parking Lot)

1. Locate Available EV Charging Spots on Floor 2 of Lot 101

Retrieves physical spots configured for Electric Vehicles that are currently open:

SELECT s.spot_id, s.spot_number, s.spot_type, s.status
FROM parking_spots s
JOIN parking_floors f ON s.floor_id = f.floor_id
WHERE f.lot_id = 101
  AND f.floor_number = 2
  AND s.spot_type = 'ELECTRIC_VEHICLE'
  AND s.status = 'AVAILABLE'
ORDER BY s.spot_number;

2. Generate Real-time Billing Total (Dynamic Calculation)

Computes bill totals using elapsed hours, base rates, weekend multipliers, and adding a $5.00 charging connection premium for Electric Vehicles:

SELECT t.ticket_id, t.license_plate,
       t.entry_time,
       t.exit_time,
       -- Calculate elapsed hours ceiling
       CEIL(EXTRACT(EPOCH FROM (COALESCE(t.exit_time, CURRENT_TIMESTAMP) - t.entry_time)) / 3600) AS hours_parked,
       -- Match base rates and apply weekend surge multipliers
       CASE 
           -- Check if entry was a weekend (Saturday = 6, Sunday = 0)
           WHEN EXTRACT(DOW FROM t.entry_time) IN (0, 6) 
           THEN r.hourly_rate * r.weekend_multiplier
           ELSE r.hourly_rate
       END AS active_hourly_rate,
       -- Add a flat $5.00 connection surcharge specifically for EV spots
       (CEIL(EXTRACT(EPOCH FROM (COALESCE(t.exit_time, CURRENT_TIMESTAMP) - t.entry_time)) / 3600) * 
        (CASE WHEN EXTRACT(DOW FROM t.entry_time) IN (0, 6) THEN r.hourly_rate * r.weekend_multiplier ELSE r.hourly_rate END))
       + (CASE WHEN s.spot_type = 'ELECTRIC_VEHICLE' THEN 5.00 ELSE 0.00 END) AS dynamic_calculated_total
FROM tickets t
JOIN parking_spots s ON t.spot_id = s.spot_id
JOIN parking_floors f ON s.floor_id = f.floor_id
JOIN parking_rates r ON f.lot_id = r.lot_id AND s.spot_type = r.spot_type
WHERE t.ticket_id = 98765;

3. Generate Dashboard Occupancy Report by Parking Structure

Aggregates and computes physical occupancy metrics across all garages:

SELECT l.lot_id, l.name AS lot_name,
       l.total_capacity,
       COUNT(s.spot_id) FILTER (WHERE s.status = 'OCCUPIED') AS occupied_spots,
       COUNT(s.spot_id) FILTER (WHERE s.status = 'AVAILABLE') AS available_spots,
       ROUND((COUNT(s.spot_id) FILTER (WHERE s.status = 'OCCUPIED') * 100.0) / l.total_capacity, 2) AS occupancy_percentage
FROM parking_lots l
JOIN parking_floors f ON l.lot_id = f.lot_id
JOIN parking_spots s ON f.floor_id = s.floor_id
GROUP BY l.lot_id, l.name, l.total_capacity
ORDER BY occupancy_percentage DESC;

This content is reserved for Premium Members.